<?php

namespace p5\objects;

/**
 * Created by PhpStorm.
 * User: simon
 * Date: 2015/11/23
 * Time: 10:00
 */
class SqlMaker
{

    public static function is_system_column($column)
    {
        if (strlen($column) <= 7) {
            return false;
        }
        $temp = substr($column, 0, 7);
        return $temp == "button_" || $temp == "system_" || $temp == "search_";
    }

    public static function is_ignore_column($column)
    {
        if (strlen($column) <= 7) {
            return false;
        }
        $temp = substr($column, 0, 7);
        return $temp == "ignore_";
    }

    public static function get_insert_column_string($matrix)
    {
        $systemArgs = ["PHPSESSID"];
        $keys = array_keys($matrix);
        $list = [];
        foreach ($keys as $key) {
            if (empty($key) || in_array($key, $systemArgs)) {
                continue;
            }
            $key = strtolower($key);
            if (SqlMaker::is_system_column($key)) {
                continue;
            }
            $column = SqlMaker::is_ignore_column($key) ? substr($key, 7) : $key;
            array_push($list, $column);
        }
        return implode(",", $list);
    }

    public static function get_insert_value_string($matrix)
    {
        $matrix = array_diff_assoc($matrix, array("PHPSESSID" => "1"));
        $colstr = "";
        while (list($key, $value) = each($matrix)) {
            $key = strtolower($key);
            if (SqlMaker::is_system_column($key)) {
                continue;
            }
            if ($key == "") {
                continue;
            }
            if ($value . "" == "") {
                $colstr .= ",NULL";
            } else {

                $temp = SqlMaker::sql_safe_replace($value);
                $value = SqlMaker::is_ignore_column($key) ? $value : "'" . $temp . "'";
                $colstr .= "," . $value;
            }
        }
        return substr($colstr, 1);
    }

    public static function get_insert_sql_body($matrix = array())
    {
        $matrix = empty($matrix) ? RequestUtils::get_parameters() : $matrix;
        $colstr = SqlMaker::get_insert_column_string($matrix);
        $valstr = SqlMaker::get_insert_value_string($matrix);
        return '(' . ($colstr) . ') VALUES (' . ($valstr) . ')';
    }


    public static function get_update_sql_body($matrix = array())
    {
        $matrix = empty($matrix) ? RequestUtils::get_parameters() : $matrix;
        $matrix = array_diff_assoc($matrix, array("PHPSESSID" => "1"));
        $colstr = "";
        while (list($key, $value) = each($matrix)) {
            $key = strtolower($key);
            if (SqlMaker::is_system_column($key)) {
                continue;
            }
            if ($key == "") {
                continue;
            }
            $column = SqlMaker::is_ignore_column($key) ? substr($key, 7) : $key;

            if ($value . "" == "") {
                $colstr .= ",$column=NULL";
            } else {
                if (!SqlMaker::is_ignore_column($key)) {
                    $value = @mysql_escape_string($value);
                    //		$value = str_replace("&#39;","''",$value) ;
                    $value = "'$value'";
                }
                $colstr .= ",$column=" . $value;
            }

        }
        return substr($colstr, 1);
    }

    public static function makeInsertIngore($table, $matrix = array())
    {
        return static::make_insert($table, $matrix, "insert ignore");
    }

    public static function make_insert($table, $matrix = array(), $insert_flag = "insert")
    {
        $matrix = empty($matrix) ? RequestUtils::get_parameters() : $matrix;
        return "$insert_flag into $table " . SqlMaker::get_insert_sql_body($matrix);
    }

    public static function make_update($table, $matrix = array(), $limit = "")
    {
        $matrix = empty($matrix) ? RequestUtils::get_parameters() : $matrix;
        return "update $table set " . SqlMaker::get_update_sql_body($matrix) . ($limit != "" ? " where " : "") . $limit;
    }


    public static function make_inserts($table, $matrix, $insert_flag = "insert")
    {
        return "$insert_flag into $table " . SqlMaker::make_insert_bodys($matrix);
    }

    public static function make_insert_bodys($matrix = array())
    {
        $matrix = empty($matrix) ? RequestUtils::get_parameters() : $matrix;
        $matrix = array_diff_assoc($matrix, array("PHPSESSID" => "1"));

        $keys = array_keys($matrix);
        $colstr = SqlMaker::get_insert_column_string($matrix);
        $len = 0;

        for ($i = 0; $i < count($keys); $i++) {
            $key = $keys[$i];
            if (is_array($matrix[$key])) {
                $len = count($matrix[$key]);
                break;
            }
        }

        $valstr = "";

        for ($i = 0; $i < $len; $i++) {
            $temp = array();
            for ($t = 0; $t < count($keys); $t++) {
                $object = $matrix[$keys[$t]];
                if (is_array($object)) {
                    $temp[$keys[$t]] = $object[$i];
                } else {
                    $temp[$keys[$t]] = $object;
                }
            }
            if ($i > 0) {
                $valstr .= ",";
            }
            $valstr .= "(";
            $valstr .= SqlMaker::get_insert_value_string($temp);
            $valstr .= ")";
        }
        return "($colstr) values $valstr ";
    }


    static public function make_limits($matrix = [], $link = "and", $inner_link = "and", $auto_persis = true)
    {
        return self::get_sql_limit($link, $inner_link, $auto_persis, $matrix);
    }

    public static function get_sql_limit($link = "and", $inner_link = "and", $auto_persis = true, $matrix = [])
    {
        global $_SERVER, $_POST, $_GET, $_SESSION;
        $first = true;
        $limits = "";

        if ($auto_persis) {
            SessionUtils::search_parameters_to_page_session();
        }
        if (empty($matrix)) {
            $matrix = SessionUtils::get_page_session_matrix();
        }
        foreach ($matrix as $key => $value) {
            if ($value == "" || strlen($key) <= 7 || strtolower(substr($key, 0, 7)) != "search_") {
                continue;
            }
            if (!SqlMaker::sql_link_safe($value)) {
                continue;
            }
            if (!$first) {
                $limits .= " $inner_link ";
            }
            $first = false;
            $limits .= self::makeCellLimit($key, $value);
        }
        if ($limits != "") {
            $limits = " $link (" . $limits . ")";
        }
        return str_replace("__", ".", $limits);
    }

    static public function expandLimits($keyword, $columns, $link = "or")
    {
        if (empty($keyword) || empty($columns)) {
            return "";
        }
        $limits = [];
        foreach ($columns as $name) {
            $string = self::makeCellLimit($name, $keyword);
            if (!empty($string)) {
                array_push($limits, $string);
            }
        }
        if (empty($limits)) {
            return "";
        }
        return " and (" . implode(" $link ", $limits) . ")";
    }

    static public function makeCellLimit($name, $value)
    {
        $string = str_replace("__", ".", $name);
        $temp = explode("_", $string);

        $operator = $temp[1];
        $column = substr($string, 10);

        $limits = "";
        if ($operator == "lk") {
            //full like
            $limits .= $column . " like '%" . $value . "%'";
        } else if ($operator == "ll") {
            //left like
            $limits .= $column . " like '" . $value . "%'";
        } else if ($operator == "rl") {
            //right like
            $limits .= $column . " like '%" . $value . "'";
        } else if ($operator == "eq") {
            //string equals
            $limits .= $column . "='" . $value . "'";
        } else if ($operator == "ne") {
            //string not equals
            $limits .= $column . "!='" . $value . "'";
        } else if ($operator == "ie") {
            //integer equals
            $limits .= $column . "=" . $value;
        } else if ($operator == "be") {
            //big and equals
            $limits .= $column . ">=" . $value;
        } else if ($operator == "bt") {
            //big than
            $limits .= $column . ">" . $value;
        } else if ($operator == "le") {
            //less and equals
            $limits .= $column . "<=" . $value;
        } else if ($operator == "lt") {
            //less than
            $limits .= $column . "<" . $value;
        } else if ($operator == "t1") {
            $limits .= $column . ">='" . $value . "'";
        } else if ($operator == "t2") {
            $limits .= $column . "<='" . $value . "'";
        } else if ($operator == "tr" || $operator === "df") {
            // tr = time range
            $limits .= self::get_date_limit($column, $value);
        } else if ($operator == "in") {
            $string = $value;
            if (is_array($value)) {
                $string = implode(',', $value);
            }
            $limits .= $column . " in (" . $string . ")";
        } else if ($operator == "ins") {
            if (!is_array($value)) {
                $value = explode(",", $value);
            }
            $string = implode("','", $value);
            $limits .= $column . " in ('" . $string . "')";
        } else {
            $limits .= " '" . $value . "' ";
        }
        return $limits;
    }

    public static function sql_link_safe($string)
    {
        if ($string === null) {
            return true;
        }
        if (preg_match("/^[\d\w]+$/", $string)) {
            return true;
        }
        if (preg_match("/\s(or|and|union|select|update|delete|not|where)\s/i", $string)) {
            return false;
        }
        if (preg_match("/\(\)/", $string)) {
            return false;
        }
        return true;
    }

    public static function sql_safe_replace($string)
    {
        $string = str_replace("'", "''", $string);
        return $string;
    }

    public static function get_date_limit($column, $flag)
    {
        if ($flag == "today") {
            return " $column >= '" . TimeUtils::current_date() . "' and $column < '" . TimeUtils::tomorrow() . "'";
        }
        if ($flag == "yesterday") {
            return " $column >= '" . TimeUtils::yesterday() . "' and $column < '" . TimeUtils::current_date() . "'";
        }
        if ($flag == "tomorrow") {
            return " $column >= '" . TimeUtils::offset_n_day(1) . "' and $column < '" . TimeUtils::offset_n_day(2) . "'";
        }
        if ($flag == "last_7_day") {
            return " $column >= '" . TimeUtils::offset_n_day(-6) . "' and $column < '" . TimeUtils::offset_n_day(1) . "'";
        }
        if ($flag == "this_month") {
            return " $column >= '" . TimeUtils::get_current_month_first_day() . "' and $column < '" . TimeUtils::get_next_month_first_day() . "'";
        }
        if ($flag == "last_month") {
            return " $column >= '" . TimeUtils::get_last_month_first_day() . "' and $column < '" . TimeUtils::get_current_month_first_day() . "'";
        }
        if ($flag == "last_30_day") {
            return " $column >= '" . TimeUtils::offset_n_day(-29) . "' and $column < '" . TimeUtils::offset_n_day(1) . "'";
        }
        if ($flag == "next_7_day") {
            return " $column >= '" . TimeUtils::offset_n_day(0) . "' and $column < '" . TimeUtils::offset_n_day(7) . "'";
        }
        if ($flag == "next_week") {
            $t1 = TimeUtils::get_next_week_begin();
            $d1 = date('Y-m-d', $t1);
            $d2 = date('Y-m-d', TimeUtils::time_offset($t1, 7, 'day'));
            return "  $column >= '$d1' and $column < '$d2'";
        }
        if ($flag == "this_week") {
            $t1 = TimeUtils::get_next_week_begin();
            $d1 = date('Y-m-d', $t1);
            $d2 = date('Y-m-d', TimeUtils::time_offset($t1, -8, 'day'));
            return "  $column >= '$d2' and $column < '$d1'";
        }
        if ($flag == "last_week") {
            $t1 = TimeUtils::get_next_week_begin();
            $d1 = date('Y-m-d', TimeUtils::time_offset($t1, -15, 'day'));
            $d2 = date('Y-m-d', TimeUtils::time_offset($t1, -8, 'day'));
            return "  $column >= '$d1' and $column < '$d2'";
        }
        if ($flag == "this_year") {
            $y = date('Y', time());
            $d1 = $y . "-12-31";
            $d2 = $y . "-01-01";
            return " $column >= '$d2' and $column <= '$d1'";
        }
        if ($flag == "last_year") {
            $y = date('Y', time()) * 1 - 1;
            $d1 = $y . "-12-31";
            $d2 = $y . "-01-01";
            return " $column >= '$d2' and $column <= '$d1'";
        }
        return "";
    }


}
